﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using UncleBobWebService.Models;
using UncleBobWebService.ServiceRequester;


namespace UncleBobWebService.Database
{
    class HiringManagerDBAdapter : IDBAdapter
    {

        public override Message CreateData(Message msg, Object applicant)
        {
            msg.Result = true;

            JobApplication jobApp = (JobApplication)msg.Data;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = string.Format("UPDATE JobApplications SET Reviewed='{0}' WHERE JobApplicationID='{1}'", "True", jobApp.AppID);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }
                finally
                {
                    connection.Close();
                }
            }
            
            return msg;
        }


        public override Message SearchForSpecificData(Message msg, Object user)
        {
            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format(
            "SELECT DISTINCT UserRegistration.FirstName, UserRegistration.LastName, UserRegistration.UserID FROM UserRegistration INNER JOIN JobApplications ON JobApplications.UserID=UserRegistration.UserID ORDER BY UserRegistration.LastName;"
            );
           


            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);
           
            HMRegisterModel hmModel = new HMRegisterModel();
            HMRegisterModel hmTempModel = new HMRegisterModel();

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                hmModel.apps = new List<HMRegisterModel>();
                    
         


                if (mTable.Rows.Count != 0)
                {
                    int mCount = mTable.Rows.Count;

                    do
                    {
                        msg.Result = true;
                        Message message = new Message();                                              
                        hmTempModel = new HMRegisterModel();
                        
                        string firstName = mTable.Rows[mCount - 1]["LastName"].ToString();
                        string lastName = mTable.Rows[mCount - 1]["FirstName"].ToString();
                        string userId = mTable.Rows[mCount - 1]["UserID"].ToString();
                        hmTempModel.FirstName = firstName;
                        hmTempModel.LastName = lastName;
                        hmTempModel.UserID = userId;
                                                
                        hmModel.apps.Add(hmTempModel);
                      

                        mCount--;
                    } while (mCount > 0);

               
                }
                
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            msg = new Message(hmModel);
            return msg;
        }


        public override Message SearchForAllData(Message msg)
        {           
                mTable = new DataTable();
                mCommand = mConnection.CreateCommand();
                mCommand.CommandText = string.Format(
                "SELECT * FROM Jobs AS jt LEFT JOIN JobApplications AS ja ON jt.JobID = ja.JobID"
                );



                SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

                HMRegisterModel hmModel = new HMRegisterModel();
                HMRegisterModel hmTempModel = new HMRegisterModel();

                try
                {
                    mConnection.Open();
                    mAdapter.Fill(mTable);
                    mConnection.Close();

                    hmModel.jobApps = new List<JobApplication>();




                    if (mTable.Rows.Count != 0)
                    {
                        int mCount = mTable.Rows.Count;

                        do
                        {
                            msg.Result = true;
                            Message message = new Message();
                            hmTempModel = new HMRegisterModel();

                            JobApplication jobApp = new JobApplication();
                            JobModel jobModel = new JobModel();


                            jobApp.AppID = Convert.ToInt32(mTable.Rows[mCount - 1]["JobApplicationID"].ToString());

                            jobApp.ApplyDate = (DateTime)mTable.Rows[mCount - 1]["ApplyDate"];
                            jobApp.UserID = Convert.ToInt32(mTable.Rows[mCount - 1]["UserID"].ToString());
                            jobModel.ID = Convert.ToInt32(mTable.Rows[mCount - 1]["JobID"].ToString());
                            jobApp.Reviewed = Convert.ToBoolean(mTable.Rows[mCount - 1]["Reviewed"]);
                            

                            jobModel.Location = mTable.Rows[mCount - 1]["JobLocation"].ToString();
                            jobModel.Title = mTable.Rows[mCount - 1]["JobTitle"].ToString();
                            

                            jobApp.Job = jobModel;


                            hmModel.jobApps.Add(jobApp);


                            mCount--;
                        } while (mCount > 0);


                    }

                }
                catch (Exception error)
                {
                    msg.Result = false;
                    msg.Data = new String(error.Message.ToCharArray());
                }

                msg = new Message(hmModel);
                return msg;
        }


        public override Message EditSpecificData(Message msg, Object data)
        {
            throw new NotImplementedException();
        }


        public override Message DeleteSpecificData(Message msg, Object data)
        {
            throw new NotImplementedException();
        }


        public override Message DeleteAllData(Message msg)
        {
            throw new NotImplementedException();
        }
    }
}
